package controller;

import com.mongodb.*;
import com.mongodb.client.AggregateIterable;
import entity.User;
import entity.Users;
import lombok.extern.slf4j.Slf4j;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.bson.Document;
import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.context.annotation.Role;
import org.springframework.data.domain.Sort.Order;
import org.springframework.data.domain.Sort;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.*;
import org.springframework.data.mongodb.core.index.MongoMappingEventPublisher;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.core.query.Update;
import org.springframework.util.ResourceUtils;
import org.springframework.web.bind.annotation.*;

import javax.jws.soap.SOAPBinding;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;

import static org.springframework.data.mongodb.core.aggregation.Aggregation.group;
import static org.springframework.data.mongodb.core.aggregation.Aggregation.sort;

@RestController
@RequestMapping("/mongodbController")
@Slf4j
public class MongodbController {

    @Autowired
    private MongoTemplate mongoTemplate;

    private static final String collectionName = "user";

    @RequestMapping(value = "/export", method = RequestMethod.GET)
    public void excel(HttpServletResponse response) {

        /**
         * excel导出
         * 1.获取数据集List 插入到map集合中
         * 2.根据模板生成新的excel
         * 3.将新生成的excel文件从浏览器输出
         * 4.删除新生成的模板文件
         */
        List<Users> list = new ArrayList();
        list.add(new Users(1, "zs", 21, "21343-4343-234"));
        list.add(new Users(2, "ls", 22, "21343-4343-234"));
        Map<String, Object> beans = new HashMap();
        beans.put("list", list);

        //加载excel模板文件
        File file = null;
        try {
            file = ResourceUtils.getFile("classpath:excel/aaa.xlsx");
        } catch (FileNotFoundException e) {
            log.error("template file path cannot be found");
        }

        //配置下载路径
        String path = "/download/";
        createDir(new File(path));

        //根据模板生成新的excel
        File excelFile = createNewFile(beans, file, path);

        //浏览器端下载文件
        downloadFile(response, excelFile);

        //删除服务器生成文件
        deleteFile(excelFile);

    }


    @GetMapping("/insert")
    @ResponseBody
     public void insert() throws Exception {
        System.out.println("进来了");
        User user = new User();
/*        user.setAge(50);
        user.setBirthday(new Date());
        user.setDataStatus(1);
        user.setEmail("jwjgtg");
        user.setName("huhao");
        user.setUserId("3433");
        user.setUclass("werf");*/
      this.mongoTemplate.insert(user);
     }
    /**
     * 根据excel模板生成新的excel
     *
     * @param beans
     * @param file
     * @param path
     * @return
     */
    private File createNewFile(Map<String, Object> beans, File file, String path) {
        XLSTransformer transformer = new XLSTransformer();

        //可以写工具类来生成命名规则
        String name = "bbb.xlsx";
        File newFile = new File(path + name);


        try (InputStream in = new BufferedInputStream(new FileInputStream(file));
             OutputStream out = new FileOutputStream(newFile)) {
            Workbook workbook = transformer.transformXLS(in, beans);
            workbook.write(out);
            out.flush();
            return newFile;
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return newFile;
    }

    /**
     * 将服务器新生成的excel从浏览器下载
     *
     * @param response
     * @param excelFile
     */
    private void downloadFile(HttpServletResponse response, File excelFile) {
        /* 设置文件ContentType类型，这样设置，会自动判断下载文件类型 */
        response.setContentType("multipart/form-data");
        /* 设置文件头：最后一个参数是设置下载文件名 */
        response.setHeader("Content-Disposition", "attachment;filename=" + excelFile.getName());
        try (
                InputStream ins = new FileInputStream(excelFile);
                OutputStream os = response.getOutputStream()
        ) {
            byte[] b = new byte[1024];
            int len;
            while ((len = ins.read(b)) > 0) {
                os.write(b, 0, len);
            }
        } catch (IOException ioe) {
            ioe.printStackTrace();
        }
    }

    /**
     * 浏览器下载完成之后删除服务器生成的文件
     * 也可以设置定时任务去删除服务器文件
     *
     * @param excelFile
     */
    private void deleteFile(File excelFile) {

        excelFile.delete();
    }

    //如果目录不存在创建目录 存在则不创建
    private void createDir(File file) {
        if (!file.exists()) {
            file.mkdirs();
        }
    }
     @GetMapping("/delete")
     @ResponseBody
    public void delete() throws Exception {
                 Query query = Query.query(Criteria.where("userId").is("34343"));
                this.mongoTemplate.remove(query, collectionName);
             }
             @GetMapping("/update")
             @ResponseBody
    public void update() throws Exception {
        Query query = Query.query(Criteria.where("userId").is("34343"));
        Update update = new Update();
        update.set("age", 30);
        update.set("name", "haha");
        update.set("email","wuhang");
        this.mongoTemplate.updateFirst(query, update, collectionName);

    }

    @GetMapping("/query")
    @ResponseBody
    public void querys() {
/*        Query query =new Query();
        query.addCriteria(new Criteria("dataStatus").is(1));
        query.with(new Sort(new Sort.Order(Sort.Direction.DESC,"age")));
        query.limit(3);
        query.skip((2-1)*2);
        List<User> users = this.mongoTemplate.find(query, User.class);
        int age=0;
        for (User use : users) {
            age=use.getAge();
        }
        return age;*/


/*        Aggregation aggregation = Aggregation.newAggregation(
                Aggregation.sort(new Sort(Sort.Direction.DESC,"_id")),//排序，可以忽略
                Aggregation.match(Criteria.where("name").is("haha"))
        );
        AggregationResults<User> ar = mongoTemplate.aggregate(aggregation,"user", User.class);

        List<User> list = ((AggregationResults) ar).getMappedResults();//如果成功该List就是我想要的结果
        for (User ranking : list) {
            System.out.println(ranking.getAge()+":"+ranking.getName());
        }*/


/*        TypedAggregation<User> agg = Aggregation.newAggregation(User.class,
                Aggregation.group("name").
                        sum("age").as("agesum").
                        first("age").as("agefirst").max("age").as("hahd")
        );
        AggregationResults<Document> result = mongoTemplate.aggregate(agg,Document.class);
        result.getMappedResults().forEach(document -> System.out.println(document));*/


/*        Criteria criteria = Criteria.where("email").is("wuhang");
        Aggregation customerAgg = Aggregation.newAggregation(
                Aggregation.project("userId","name","uclass","email","age","dataStatus"),
                Aggregation.match(criteria),
                Aggregation.unwind("userId"),
                Aggregation.sort(new Sort(new Sort.Order(Sort.Direction.DESC, "age")))
        );
        AggregationResults<User> aggregate = this.mongoTemplate.aggregate(customerAgg, collectionName,User.class);
        List<User> customerDetails = aggregate.getMappedResults();
        for (User ranking : customerDetails) {
            System.out.println(ranking.getAge()+":"+ranking.getName()+":"+ranking.getEmail());
        }*/
;
        Aggregation agg = Aggregation.newAggregation(
                User.class,
                Aggregation.project("name"),
                group("name").count().as("count"),
                sort(Sort.Direction.DESC, "count")

        );


        AggregationResults<User> groupResults
                = mongoTemplate.aggregate(agg,collectionName,User.class);

        List<User> result = groupResults.getMappedResults();
 /*       for (User ranking : result) {
            System.out.println(ranking.getCount()+":"+ranking.getName());
        }
*/

    }
}
